﻿
using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
namespace GPS.Controllers.GPS_BaoCao
{
    public class GPS_GS_StopController : Controller
    {
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        //
        // GET: /GPS_GS_Stop/
        #region Index
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public ActionResult Index(ViewReportModelGPS_GS_STOP SearchModel, int? currentPage)
        {
            string DVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            ViewBag.checkuser = DVGTVT_MA;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, SearchModel.pDVGTVT_MA);
            SearchModel.pDVGTVT_MA = getdvgtvt_Ma;
            TempData["DVGTVT_MA"] = getdvgtvt_Ma;

            var entities = Getdata(SearchModel);
            var model = new List<RepostModelGPS_GS_STOP>();
            foreach (var currentEntity in entities)
            {
                model.Add(Mapper.Map<RepostModelGPS_GS_STOP>(currentEntity));
            }

            initialCategorySearchAction(SearchModel);
            if (SearchModel.ExportButton == "Xuất Excel")
            {
                try
                {
                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoDungDo.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoDungDoNEW.xlsx"));

                    // Using the template to create the newFile...
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["DrivingTimeContinuous"];

                        int recordIndex = 7;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.GPS_STOP_BIENSO == null ? "" : item.GPS_STOP_BIENSO;
                            //myWorksheet.Cells[recordIndex, 3].Value = "LÁI XE ĐĂNG XUẤT";
                            //myWorksheet.Cells[recordIndex, 4].Value = "LÁI XE ĐĂNG XUẤT";
                            myWorksheet.Cells[recordIndex, 5].Value = "";
                            myWorksheet.Cells[recordIndex, 6].Value = item.GPS_STOP_TIMEEND == null ? "" : item.GPS_STOP_TIMEEND;
                            myWorksheet.Cells[recordIndex, 7].Value = item.GPS_STOP_TIMEDIFF == null ? "" : item.GPS_STOP_TIMEDIFF;
                            myWorksheet.Cells[recordIndex, 8].Value = item.GPS_STOP_POINTSTOP == null ? "" : item.GPS_STOP_POINTSTOP;
                          //  myWorksheet.Cells[recordIndex, 9].Value = item.GPS_STOP_LNGSTTOP == null ? "" : item.GPS_STOP_LNGSTTOP;
                            myWorksheet.Cells[recordIndex, 10].Value = "";
                            recordIndex++;
                        }
                        myWorksheet.Cells[2, 1].Value = "Từ ngày " + SearchModel.pTUNGAY == null ? "" : SearchModel.pTUNGAY.ToString() + " đến ngày " + SearchModel.pDENNGAY == null ? "" : SearchModel.pDENNGAY.ToString();
                        myWorksheet.Cells[3, 1].Value = "Đơn vị kinh doanh vận tải: " + (SearchModel.pDVGTVT_MA == null || string.IsNullOrWhiteSpace(SearchModel.pDVGTVT_MA) ? "" : _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == SearchModel.pDVGTVT_MA).DVGTVT_TEN);
                        if (SearchModel.pBIEN_SO != null)
                        {
                            myWorksheet.Cells[4, 1].Value = "Biển số: " + SearchModel.pBIEN_SO;
                        }
                        // Saving the change...
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoDungDoNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Concat("GPS_BaoCaoDungDo_" + DateTime.Now.ToShortDateString() + ".xlsx"));
                }
                catch (Exception ex)
                {
                    TempData["Message"] = ex.ToString();
                    return RedirectToAction("Index");
                }
            }
            else
            {
                ViewBag.TotalRow = entities.Count();
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                return View(SearchModel);
            }
        }
        [HttpPost]
        public void initialCategorySearchAction(ViewReportModelGPS_GS_STOP viewModel)
        {
            ViewBag.lstDVGTVT = new SelectList(_db.Filter<SDONVIGTVT>(x => x.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");
            var khoahocs = _db.Filter<DPT>(o => o.PT_BIENSO == viewModel.pBIEN_SO);
            ViewBag.KH_MAs = new SelectList(khoahocs, "PT_BIENSO", "PT_BIENSO");
        }
        public List<RepostModelGPS_GS_STOP> Getdata(ViewReportModelGPS_GS_STOP SearchModel)
        {
            var pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var pBIEN_SO = new OracleParameter("pBIEN_SO", OracleDbType.Varchar2, (SearchModel.pBIEN_SO == null ? DBNull.Value : (object)SearchModel.pBIEN_SO), ParameterDirection.Input);
            var pTUNGAY = new OracleParameter("pTUNGAY", OracleDbType.Date, (SearchModel.pTUNGAY == null ? DBNull.Value : (object)SearchModel.pTUNGAY), ParameterDirection.Input);
            var pDENNGAY = new OracleParameter("pDENNGAY", OracleDbType.Date, (SearchModel.pDENNGAY == null ? DBNull.Value : (object)SearchModel.pDENNGAY), ParameterDirection.Input);
            var pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<RepostModelGPS_GS_STOP>("BEGIN GPS_GIAMSAT.GPS_GIAMSAT_STOP(:pDVGTVT_MA,:pBIEN_SO,:pTUNGAY,:pDENNGAY,:pRET); end;", pDVGTVT_MA, pBIEN_SO, pTUNGAY, pDENNGAY, pRET).ToList();
            return entities;
        }
        public ActionResult getBienSo(string ma_DVGT)
        {
            if (ma_DVGT == null)
            {
                ma_DVGT = this.getCurrentUser().MA_TINHTP;
            }
            var districts = _db.Filter<DPT>(d => d.PT_DVGTVT_MA == ma_DVGT).Select(a => "<option value='" + a.PT_BIENSO + "'>" + a.PT_BIENSO + "</option>");
            return Content(string.Join("", districts));
        }
        #endregion
    }
}